/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cn.ekuma.epos.datalogic.define.dao.erp;

import e.odbo.data.dao.ViewDAO;
import com.openbravo.data.basic.BasicException;
import com.openbravo.data.loader.serialize.DataParams;
import com.openbravo.data.loader.serialize.DataRead;
import com.openbravo.data.loader.serialize.Datas;
import com.openbravo.data.loader.I_Session;
import com.openbravo.data.loader.sentence.PreparedSentence;
import com.openbravo.data.loader.sqlbuilder.QBFBuilder;
import com.openbravo.data.loader.serialize.serializer.write.SerializerWriteBasic;
import com.openbravo.data.loader.serialize.serializer.write.SerializerWriteParams;
import com.openbravo.data.loader.sentence.StaticSentence;
import com.openbravo.data.loader.query.QBFCompareEnum;
import com.openbravo.data.loader.query.QBFParameters;
import com.openbravo.bean.ProductLocationState;
import com.openbravo.bean.erp.viewbean.ProductAnalysis;

import java.util.Date;
import java.util.List;

/**
 *
 * @author Administrator
 */
public class ProductAnalysisDAO extends ViewDAO<ProductAnalysis>{

    public ProductAnalysisDAO(I_Session s) {
        super(s);
    }

    public ProductAnalysis readValues(DataRead dr,ProductAnalysis p) throws BasicException {
         if(p==null)
        	 p=new ProductAnalysis();
            p.setM_ID(dr.getString(1));
            p.setM_sRef(dr.getString(2));
            p.setM_sCode(dr.getString(3));
            p.setM_sName(dr.getString(4));
            p.setM_bCom(dr.getBoolean(5));
            p.setM_bScale(dr.getBoolean(6));
            p.setM_dPriceBuy( dr.getDouble(7));
            p.setM_dPriceSell(dr.getDouble(8));
            p.setM_dCustomerPrice(dr.getDouble(9));
            p.setCategory(dr.getString(10));
            p.setAttributeset(dr.getString(11));
            p.setM_origin(dr.getString(12));
            p.setM_unit(dr.getString(13));
            p.setM_quantity(dr.getDouble(14));
            p.setM_sSimilarCode(dr.getString(15));
            p.setM_sManufacturer(dr.getString(16));
            p.setM_iDurability(dr.getInt(17));
            p.setMinimum(dr.getDouble(18)==null?0:dr.getDouble(18));
            p.setMaximum(dr.getDouble(19)==null?0:dr.getDouble(19));
            p.setState(dr.getInt(20)==null?ProductLocationState.STATE_INOUT:dr.getInt(20));
            p.setMultiply(dr.getDouble(21));
         return p;
    }

    @Override
    public Class getSuportClass() {
        return ProductAnalysis.class;
    }

    public List<ProductAnalysis> list(Object filter) throws BasicException {
                return new StaticSentence(s, new QBFBuilder(
                "SELECT S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE,SUM(S.UNITS) "
                + "FROM STOCKCURRENT S "
                + "LEFT JOIN PRODUCTS P ON S.PRODUCT = P.ID "
                + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
                + "LEFT OUTER JOIN STOCKLEVEL L ON S.PRODUCT = L.PRODUCT  "
                + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
                + "LEFT OUTER JOIN OPERATIONPRODUCT OP ON S.PRODUCT = OP.PRODUCT  "
                + "LEFT OUTER JOIN OPERATIONPRODUCTCATEGORIES OPC ON OP.OPERATIONCATEGORIE = OPC.ID  "
                + "LEFT OUTER JOIN LOCATION_LAYOUT_CURRENT LLC ON S.PRODUCT = LLC.PRODUCT   "
                + "WHERE ?(QBF_FILTER) "
                + "GROUP BY S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE ",
                new String[]{"S.LOCATION", "S.PRODUCT", "P.CATEGORY","OPC.ID","S.UNITS","LLC.LOCATIONLAYOUT"}), new SerializerWriteBasic(new Datas[]{Datas.OBJECT, Datas.STRING, Datas.OBJECT, Datas.OBJECTS, Datas.OBJECT, Datas.OBJECTS, Datas.OBJECT, Datas.STRING,Datas.OBJECT, Datas.DOUBLE, Datas.OBJECT, Datas.STRING}), this).list(filter);
    }
    
    public List<ProductAnalysis> listProductAnalysisByCustomer(final String location,final String customer) throws BasicException {
    	return new PreparedSentence(s, 
                "SELECT S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE,SUM(S.UNITS) "
                + "FROM STOCKCURRENT S "
                + "LEFT JOIN PRODUCTS P ON S.PRODUCT = P.ID "
                + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
                + "LEFT OUTER JOIN STOCKLEVEL L ON S.PRODUCT = L.PRODUCT  "
                + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
                + "WHERE S.LOCATION=? AND S.PRODUCT IN(SELECT PRODUCT_ID FROM CUSTOMER_PRODUCT WHERE CUSTOMER_ID=?) "
                + "GROUP BY S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE ", SerializerWriteParams.INSTANCE, this)
		.list(new DataParams() {
			public void writeValues() throws BasicException {
				setString(1, location);
				setString(2, customer);
			}
		});
    }
    
    public List<ProductAnalysis> listProductAnalysisByProductCurDate(Date startdate, Date enddate) throws BasicException{
    	Object[] afilter=new Object[4];
    	afilter[0] = (startdate == null) ? QBFCompareEnum.COMP_NONE : QBFCompareEnum.COMP_GREATEROREQUALS;
        afilter[1] = startdate;
        afilter[2] = (enddate == null) ? QBFCompareEnum.COMP_NONE : QBFCompareEnum.COMP_LESS;
        afilter[3] = enddate;
	
    	return new StaticSentence(s, new QBFBuilder(
                "SELECT S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE,SUM(S.UNITS) "
                + "FROM STOCKCURRENT S "
                + "LEFT JOIN PRODUCTS P ON S.PRODUCT = P.ID "
                + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
                + "LEFT OUTER JOIN STOCKLEVEL L ON S.PRODUCT = L.PRODUCT  "
                + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
                + "WHERE ?(QBF_FILTER) "
                + "GROUP BY S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE ",
                new String[]{"P.CURDATE", "P.CURDATE"}), new SerializerWriteBasic(new Datas[]{Datas.OBJECT, Datas.TIMESTAMP, Datas.OBJECT, Datas.TIMESTAMP}), this).list(afilter);
    }
    
    public List<ProductAnalysis> list(QBFParameters paras) throws BasicException {
        return new StaticSentence(s, new QBFBuilder(
        "SELECT S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE,SUM(S.UNITS) "
        + "FROM STOCKCURRENT S "
        + "LEFT JOIN PRODUCTS P ON S.PRODUCT = P.ID "
        + "LEFT OUTER JOIN CATEGORIES C ON P.CATEGORY = C.ID "
        + "LEFT OUTER JOIN STOCKLEVEL L ON S.PRODUCT = L.PRODUCT  "
        + "LEFT OUTER JOIN PRODUCT_LOCATION_STATE PL ON S.PRODUCT = PL.PRODUCT_ID AND S.LOCATION=PL.LOCATION_ID  "
        + "LEFT OUTER JOIN OPERATIONPRODUCT OP ON S.PRODUCT = OP.PRODUCT  "
        + "LEFT OUTER JOIN OPERATIONPRODUCTCATEGORIES OPC ON OP.OPERATIONCATEGORIE = OPC.ID  "
        + "LEFT OUTER JOIN LOCATION_LAYOUT_CURRENT LLC ON S.PRODUCT = LLC.PRODUCT   "
        + "WHERE ?(QBF_FILTER) "
        + "GROUP BY S.PRODUCT, P.REFERENCE, P.CODE, P.NAME, P.ISCOM, P.ISSCALE, P.PRICEBUY, P.PRICESELL, P.CUSTOMERPRICESELL,C.NAME,P.ATTRIBUTESET_ID,P.ORIGIN,P.UNIT,P.QUANTITY,P.SIMILARCODE,P.MANUFACTUR,P.DURABILITY,L.STOCKSECURITY,L.STOCKMAXIMUM,PL.STATE "), null, this).list(paras);
   }
}
